/* ORACLE 1
mafra.coti@gmail.com */

/*
Oltp -> on line transactional process
DATAWAREHOUSE
Olap -> on line analytical process
modelagem estrela - snow flake
*/

/* no prompt do dos*/

sqlplus system/coti

/* nome do banco */
select name from v$database;

/* SELECAO - PROJECAO - JUNCAO */
select 10 + 10 FROM DUAL;

SELECT * FROM DUAL;

/* USUARIO LOGADO NO BANCO */
SELECT USER FROM DUAL;

/* TRABALHANDO COM TABELAS
DDL - DATA DEFINITION LANGUAGE
*/

CREATE TABLE VENDAS(
	ID NUMBER(5),
	NOME VARCHAR(35),
	PRODUTO VARCHAR(20),
	PRECO NUMBER(10,2) CHECK (PRECO > 0),
	PRIMARY KEY(ID)
);

/* SEQUENCES 
SERIAL -> POSTGRE, SQL SERVER
AUTO_INCREMENT -> MYSQL
*/

CREATE SEQUENCE SEQ_VENDAS;

CREATE SEQUENCE SEQ_VENDAS2
START WITH 1000
INCREMENT BY 20
CACHE 30;

CREATE SEQUENCE SEQ_VENDAS3
MINVALUE 1000
MAXVALUE 4000;

/* INSERINDO VALORES */

INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'JOSE', 'TENIS', 500);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'JOAO', 'CAMISA', 100);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'MIRIAM', 'CALCA', 160);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'ANDRE', 'TENIS', 400);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'ANA', 'CAMISA', 500);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'CLEIDE', 'CALCA', 200);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'BELEM', 'ESPARTILHO', 100);
INSERT INTO VENDAS VALUES(SEQ_VENDAS.NEXTVAL, 'JULIAO', 'ESPARTILHO', 500);

/* VERIFICANDO SEQUENCES */

SELECT SEQ_VENDAS.CURRVAL FROM DUAL;
SELECT SEQ_VENDAS.NEXTVAL FROM DUAL;

/*  VERIFICANDO DADOS */

SELECT * FROM VENDAS;

/* ACID - ATOMICIDADE, CONSISTENCIA, ISOLAMENTO E DURABILIDADE 
DML - DATA MANIPULATION LANGUAGE
*/

/* COMANDOS TRANSACIONAIS SO AFETAM COMANDOS DE DML */

ROLLBACK;

SELECT * FROM VENDAS;

COMMIT;

SELECT * FROM VENDAS;

DELETE FROM VENDAS;

/* DDL */
TRUNCATE TABLE VENDAS;

/* INDO NO PROMPT */
HOST CLS;

/* TOTAIS POR PRODUTOS */

SELECT SUM(PRECO) AS "TOTAL",
	   PRODUTO
	   FROM VENDAS
	   GROUP BY PRODUTO;
	   
SELECT SUM(PRECO) TOTAL, /*ALIAS - APELIDO*/
	   PRODUTO
	   FROM VENDAS
	   GROUP BY PRODUTO;
	   
SELECT SUM(PRECO) AS TOTAL,
	   PRODUTO
	   FROM VENDAS
	   GROUP BY PRODUTO;
	   
   
/* ROLLUP -> SOMATORIO TOTAL DO AGRUPAMENTO */

SELECT SUM(PRECO) AS "TOTAL",
	   PRODUTO
	   FROM VENDAS
	   GROUP BY ROLLUP(PRODUTO);
	   
/* TRABALHANDO COM CONDICOES */

SELECT DECODE(PRODUTO, 'CAMISA', 'PRODUTO 1',
					   'CALCA', 'PRODUTO 2',
					   'TENIS', 'PRODUTO 3',
					   'ESPARTILHO', 'JULIO') AS "FAIXA",
			PRODUTO, PRECO 
			FROM VENDAS;
			
/* INSERINDO CONDICOES COM CASE WHEN  */

SELECT CASE
			WHEN PRECO > 100 AND PRECO < 400 THEN 'FAIXA 1'
			WHEN PRECO >= 400 THEN 'FAIXA 2'
			END AS FAIXA,
			PRODUTO, PRECO FROM VENDAS;
			
SELECT DECODE(PRODUTO, 'CAMISA', 'PRODUTO 1',
					   'CALCA', 'PRODUTO 2',
					   'TENIS', 'PRODUTO 3',
					   'ESPARTILHO', 'JULIO') AS "FAIXA 1",
CASE		WHEN PRECO > 100 AND PRECO < 400 THEN 'FAIXA 2'
			WHEN PRECO >= 400 THEN 'FAIXA 2'
			END AS FAIXA,
			PRODUTO,
			PRECO 
			FROM VENDAS;
			
SELECT PRECO
FROM VENDAS
WHERE PRODUTO = 'CAMISA';
			
/* CURSOR IMPLICITO - VARIAVEL QUE ARMAZENA
RESULTADO DE UM SELECT SEMELHANTE A UM VETOR
*/

/* ATIVANDO O SERVIDOR DE IMPRESSAO */
SET SERVEROUTPUT ON;

DECLARE
	BEGIN
		DBMS_OUTPUT.PUT_LINE('AULA 01 - ORACLE PL/SQL');
	END;
/

DECLARE
	BEGIN
		FOR REGISTRO IN (SELECT PRODUTO, PRECO FROM VENDAS) LOOP
		DBMS_OUTPUT.PUT_LINE(REGISTRO.PRODUTO || ' - ' || REGISTRO.PRECO);
		END LOOP;
	END;
/

/* TRATANDO ERROS */

DECLARE
	VPRODUTO VARCHAR(25) := '';
	BEGIN
		SELECT PRODUTO INTO VPRODUTO FROM VENDAS WHERE ID=18;
		DBMS_OUTPUT.PUT_LINE('PRODUTO BUSCADO: ' || VPRODUTO);
		
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('ERRO ENCONTRADO NA BUSCA');
	
	END;
	/
	
/* VIEWS - ORGANIZACOES LOGICAS DE DADOS
N X N - PRINCIPIO DE MODELAGEM DE DATAWAREHOURE
 */

CREATE TABLE PACIENTE(
	IDPACIENTE NUMBER(5),
	NOME VARCHAR(30)
);

CREATE TABLE MEDICO(
	IDMEDICO NUMBER(5),
	NOME VARCHAR(30),
	ESPECIALIDADE VARCHAR(20)
);

CREATE TABLE CONSULTA(
	ID_MEDICO NUMBER(5),
	ID_PACIENTE NUMBER(5)
);
	
/* CONSTRAINTS -> REGRAS DE TABELA 
http://www.baixaki.com.br/download/brmodelo.htm
*/

ALTER TABLE PACIENTE ADD CONSTRAINT PK_PACIENTE
PRIMARY KEY(IDPACIENTE);

ALTER TABLE MEDICO ADD CONSTRAINT PK_MEDICO
PRIMARY KEY(IDMEDICO);

ALTER TABLE CONSULTA ADD CONSTRAINT PK_CONSULTA
PRIMARY KEY(ID_MEDICO,ID_PACIENTE);

ALTER TABLE CONSULTA ADD CONSTRAINT FK_PACIENTE_CONSULTA
FOREIGN KEY(ID_PACIENTE) REFERENCES PACIENTE
ON DELETE CASCADE;

ALTER TABLE CONSULTA ADD CONSTRAINT FK_MEDICO_CONSULTA
FOREIGN KEY(ID_MEDICO) REFERENCES MEDICO
ON DELETE CASCADE;

/* INSERINDO VALORES */

INSERT INTO PACIENTE VALUES(1, 'JOAO');
INSERT INTO PACIENTE VALUES(2, 'RODRIGO');
INSERT INTO PACIENTE VALUES(3, 'ANTONIO');
INSERT INTO PACIENTE VALUES(4, 'ANDRE');

INSERT INTO MEDICO VALUES(1, 'JULIAO', 'UROLOGISTA');
INSERT INTO MEDICO VALUES(2, 'CARLOS', 'CARDIOLOGISTA');
INSERT INTO MEDICO VALUES(3, 'CLAUDIO', 'PEDIATRA');
INSERT INTO MEDICO VALUES(4, 'ANDRE', 'NEFROLOGISTA');

INSERT INTO CONSULTA VALUES(1,2);
INSERT INTO CONSULTA VALUES(2,1);
INSERT INTO CONSULTA VALUES(3,2);
INSERT INTO CONSULTA VALUES(1,3);
INSERT INTO CONSULTA VALUES(2,2);
INSERT INTO CONSULTA VALUES(4,4);
INSERT INTO CONSULTA VALUES(3,1);
INSERT INTO CONSULTA VALUES(1,4);

/* CRIANDO VIEWS MEDICO, PACIENTE
ESPECIALIDADE */

SELECT M.NOME, P.NOME, M.ESPECIALIDADE
FROM MEDICO M
INNER JOIN CONSULTA C
ON M.IDMEDICO = C.ID_MEDICO
INNER JOIN PACIENTE P
ON P.IDPACIENTE = C.ID_PACIENTE;


/* ORDEM DE LEITURA
SELECAO, JUNCAO E PROJECAO
WHERE -> CLAUSULA DE SELECAO */
SELECT M.NOME, P.NOME, M.ESPECIALIDADE AS "AREA"
FROM MEDICO M
INNER JOIN CONSULTA C
ON M.IDMEDICO = C.ID_MEDICO
INNER JOIN PACIENTE P
ON P.IDPACIENTE = C.ID_PACIENTE
WHERE AREA = 'UROLOGISTA';

COLUMN NOME FORMAT A10;
COLUMN ESPECIALIDADE FORMAT A15;

/* SEM USO DO WHERE PARA CLAUSULA DE SELECAO
ERRADO*/
SELECT M.NOME, P.NOME, M.ESPECIALIDADE
FROM MEDICO M
 CONSULTA C
WHERE M.IDMEDICO = C.ID_MEDICO
AND
P.IDPACIENTE = C.ID_PACIENTE;

/* CRIACAO DE VIEWS */

CREATE VIEW RELATORIO AS
SELECT M.NOME AS "MEDICO", P.NOME AS "PACIENTE", M.ESPECIALIDADE
FROM MEDICO M
INNER JOIN CONSULTA C
ON M.IDMEDICO = C.ID_MEDICO
INNER JOIN PACIENTE P
ON P.IDPACIENTE = C.ID_PACIENTE;

SELECT * FROM RELATORIO;

/*OPERADORES*/

CREATE TABLE A(
	ID NUMBER(5),
	CIDADE VARCHAR(15)
);

INSERT INTO A VALUES(1, 'RIO DE JANEIRO');
INSERT INTO A VALUES(2, 'SAO PAULO');
INSERT INTO A VALUES(3, 'PELOTAS');
INSERT INTO A VALUES(4, 'TOQUIO');
INSERT INTO A VALUES(5, 'LOS ANGELES');

CREATE TABLE B(
	ID NUMBER(5),
	CIDADE VARCHAR(15)
);
INSERT INTO B VALUES(1, 'CURITIBA');
INSERT INTO B VALUES(2, 'BELO HORIZONTE');
INSERT INTO B VALUES(3, 'PELOTAS');
INSERT INTO B VALUES(4, 'TOQUIO');
INSERT INTO B VALUES(5, 'LOS ANGELES');

/*NAO REPETIDOS*/
SELECT * FROM A
UNION
SELECT * FROM B;

CREATE VIEW CIDADES AS
SELECT CIDADE FROM A
UNION
SELECT CIDADE FROM B;

SELECT ROWNUM AS "ID", CIDADE
FROM CIDADES;

/* REPETE OS COMUNS */
SELECT * FROM A
UNION ALL
SELECT * FROM B;

/* SOMENTE QUE SE REPETEM */
SELECT * FROM A
INTERSECT
SELECT * FROM B;

/* O QUE TEM EM A MENOS O CONJUNTO DE B*/
SELECT * FROM A
MINUS
SELECT * FROM B;


































			

			
			
			
			
					   







	   
	   

































